<?php
class Ordered_deal_model extends MY_Model {
	
	var $deal_id = null;
	var $modeofpayment = null;
	var $sendasgift = null;
	var $client_id = null;
	var $receipient_fullname = null;
	var $receipient_email = null;
	var $receipient_address = null;
	var $receipient_notes = null;
	var $tenant_id = null;
	var $deal_item_quantity = null;
	var $total_amount = null;
	var $status = null;
	/*
	 * kailangan pa ba itong mga fields?.. 10/25/2012
	 *
	 * var $client_email = null;
	var $client_fullname = null;
	var $client_address = null;*/
	
	
	
	public function __construct() {
		parent::__construct();
	}

	public function findByUser(){
		
		//$sql = "select od.created_on as order_created_on , * from ordered_deal od inner join deal d on d.id = od.deal_id inner join item i on i.id = d.item_id where od.valid = ? and d.valid = ? and od.client_id = ".($this->session->userdata('client_id'));
		
		$sql = "select  od.id as ordered_deal_id , od.deal_item_quantity as deal_item_quantity,od.created_on as  order_created_on,od.*,i.*,d.* from ordered_deal od inner join deal d on d.id = od.deal_id inner join item i on i.id = d.item_id  where od.valid = ? and d.valid = ? and od.client_id = ".($this->session->userdata('client_id'));
		
		return $this->db->query($sql, array(1 , 1))->result();
		
		
		
	}
	
	public function findById($id){
		
		$sql = "select  od.id as ordered_deal_id , od.deal_item_quantity as deal_item_quantity,od.created_on as  order_created_on,od.*,i.*,d.* from ordered_deal od inner join deal d on d.id = od.deal_id inner join item i on i.id = d.item_id where od.id = ? and od.valid = ? and d.valid = ? and od.client_id = ".($this->session->userdata('client_id'));
		
		return $this->db->query($sql, array( $id ,1 , 1))->result();
	}
	
	
	
	public function findByAllOrderedDeals(){
		
		$sql = "select 
		od.status as order_status,
		od.modeofpayment as modeofpayment , 
		od.id as ordered_deal_id ,
		od.deal_item_quantity as deal_item_quantity,
		od.created_on as  order_created_on,
		od.sendasgift as sendasgift,
		od.total_amount as total_amount,
		t.name as tenant_name,
		od.created_on as od_created_on,
		concat(c.firstname,' ',c.surname) as client_fullname,
		od.*,i.*,d.*,c.*,t.*
	    from ordered_deal od inner join deal d on d.id = od.deal_id inner join item i on i.id = d.item_id";
		$sql .= " left join client c on c.id = od.client_id   left join tenant t on t.id = od.tenant_id  where  od.valid = ? and d.valid = ? order by od.id desc";
		
		return $this->db->query($sql, array( 1 , 1))->result();
	}

	public function getAllOrderedDeals($where = array(), $date) {
		$this->db->select('ordered_deal.*');
		$this->db->select('deal.dealtype');
		$this->db->select('item.name AS item_name, item.code AS item_code');
		$this->db->select('tenant.name AS tenant_name');
		$this->db->select(array('user.firstname', 'CONCAT(user.firstname," ",user.lastname) AS client_name', 'user.address AS client_address'));
		$this->db->from('ordered_deal');
		$this->db->join('user','user.id = ordered_deal.client_id');
		//$this->db->join('tenant','tenant.id = ordered_deal.tenant_id','left');
		$this->db->join('tenant','tenant.id = ordered_deal.tenant_id');
		$this->db->join('deal','deal.id = ordered_deal.deal_id');
		$this->db->join('item','item.id = deal.item_id');
		
		if(is_array($where) && count($where) > 0) {
			foreach($where as $key => $val) {
				$this->db->where($key,$val);
			}
		}
		
		if(is_array($date) && count($date) > 0) {
			$this->db->where("ordered_deal.created_on BETWEEN '" . $this->changeDateFormat($date[0]) . "' AND '" . $this->changeDateFormat($date[1]) ."'");
		}
		
		return $this->db->get();
	}
	
	/**
	 * change format from dd/mm/yyyy to yyyy-mm-dd
	 * 
	 * @param String $date
	 */
	private function changeDateFormat($date) {
		$newdate = explode('/',$date);
	
		return $newdate[2] . "-" . $newdate[0] . "-" . $newdate[1];
	}
}